﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class CycleCountTagDA : ICycleCountTagDA
    {
        private string ADJ_QTY = "@ADJ_QTY";
        private string COUNT_BY = "@COUNT_BY";
        private string COUNT_DATE = "@COUNT_DATE";
        private string COUNT_QTY = "@COUNT_QTY";
        private string CREATED_BY = "@CREATED_BY";
        private string LOC = "@LOC";
        private string LOT = "@LOT";
        private string LPN_ID = "@LPN_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string RELEASE_ID = "@RELEASE_ID";
        private string REQUEST_ID = "@REQUEST_ID";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_CYCLE_COUNT_TAG_ID_UNIQUE = " SELECT COUNT(1) FROM @_@CYCLE_COUNT_TAG WHERE RELEASE_ID = @RELEASE_ID AND TAG_NO = @TAG_NO ";
        private string SQL_DELETE_CYCLE_COUNT_TAG = " DELETE FROM @_@CYCLE_COUNT_TAG WHERE RELEASE_ID = @RELEASE_ID AND TAG_NO = @TAG_NO ";
        private string SQL_DELETE_CYCLE_COUNT_TAG_BY_RELEASE_ID = "DELETE FROM @_@CYCLE_COUNT_TAG WHERE RELEASE_ID = @RELEASE_ID ";
        private string SQL_INSERT_CYCLE_COUNT_TAG = " INSERT INTO @_@CYCLE_COUNT_TAG ( WH_ID, RELEASE_ID, TAG_NO, REQUEST_ID, OWNER_ID, SKU_ID, LOT, LOC, LPN_ID, SYS_QTY, COUNT_QTY, COUNT_BY, COUNT_DATE, ADJ_QTY, STATUS, STATUS_MSG, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @RELEASE_ID, @TAG_NO, @REQUEST_ID, @OWNER_ID, @SKU_ID, @LOT, @LOC, @LPN_ID, @SYS_QTY, @COUNT_QTY, @COUNT_BY, @COUNT_DATE, @ADJ_QTY, @STATUS, @STATUS_MSG, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_CYCLE_COUNT_TAG = " SELECT  CCT.ROWID, CCT.WH_ID, CCT.RELEASE_ID, CCT.TAG_NO, CCT.REQUEST_ID, CCT.OWNER_ID, CCT.SKU_ID, CCT.LOT, CCT.LOC, CCT.LPN_ID, CCT.SYS_QTY, CCT.COUNT_QTY, CCT.COUNT_BY, CCT.COUNT_DATE, CCT.ADJ_QTY, CCT.STATUS, CCT.STATUS_MSG, CCT.CREATED_BY, CCT.CREATED_DATE, CCT.UPDATED_BY, CCT.UPDATED_DATE,T.[TEXT] AS STATUS_D  FROM  @_@CYCLE_COUNT_TAG CCT LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = CCT.STATUS AND SCD.SYS_CODE_TYPE = 'CCTStatus' LEFT JOIN sysadm.TRANSLATION T ON 'CCTStatus.'+CCT.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE ";
        private string SQL_UPDATE_CYCLE_COUNT_TAG = " UPDATE @_@CYCLE_COUNT_TAG SET WH_ID = @WH_ID, REQUEST_ID = @REQUEST_ID, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, LOT = @LOT, LOC = @LOC, LPN_ID = @LPN_ID, SYS_QTY = @SYS_QTY, COUNT_QTY = @COUNT_QTY, COUNT_BY = @COUNT_BY, COUNT_DATE = @COUNT_DATE, ADJ_QTY = @ADJ_QTY, STATUS = @STATUS, STATUS_MSG = @STATUS_MSG, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RELEASE_ID = @RELEASE_ID AND TAG_NO = @TAG_NO  ";
        private string SQL_UPDATE_CYCLE_COUNT_TAG_RELEASE = "UPDATE @_@CYCLE_COUNT_TAG SET  COUNT_QTY = @COUNT_QTY, COUNT_BY = @COUNT_BY, COUNT_DATE = @COUNT_DATE, ADJ_QTY = @ADJ_QTY, STATUS = @STATUS,UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RELEASE_ID = @RELEASE_ID AND TAG_NO = @TAG_NO ";
        private string STATUS = "@STATUS";
        private string STATUS_MSG = "@STATUS_MSG";
        private string SYS_QTY = "@SYS_QTY";
        private string TAG_NO = "@TAG_NO";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";

        public bool CheckCycleCountTagIDUnique(string cycleCountTagID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID;\t_TAG_NO", cycleCountTagID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_CYCLE_COUNT_TAG_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckCycleCountTagIDUnique(DataBase dataBase, DbTransaction tran, string cycleCountTagID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID;_TAG_NO", cycleCountTagID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_CYCLE_COUNT_TAG_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteCycleCountTag(List<string> cycleCountTagID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < cycleCountTagID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID;\t_TAG_NO", cycleCountTagID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteCycleCountTag(DataBase dataBase, DbTransaction tran, List<string> cycleCountTagID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < cycleCountTagID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID;_TAG_NO", cycleCountTagID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteCycleCountTag(DataBase dataBase, DbTransaction tran, string releaseid, string TagNo, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", releaseid), new SqlParameter("@TAG_NO", TagNo) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteCycleCountTagByReleaseID(DataBase dataBase, DbTransaction tran, List<string> ReleaseID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < ReleaseID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", ReleaseID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CYCLE_COUNT_TAG_BY_RELEASE_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllCycleCountTag(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllCycleCountTag(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public int GetCycleCountReleaseStatus(DataBase dataBase, DbTransaction tran, string ReleaseID, string Status, string whLoginID)
        {
            string strSql = " SELECT COUNT(1) FROM @_@CYCLE_COUNT_TAG WHERE RELEASE_ID = @RELEASE_ID AND STATUS !=@STATUS ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", ReleaseID), new SqlParameter("@STATUS", Status) };
            return Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras));
        }

        public int GetCycleCountRequestStatus(DataBase dataBase, DbTransaction tran, string RequestID, string Status, string whLoginID)
        {
            string strSql = " SELECT COUNT(1) FROM @_@CYCLE_COUNT_TAG WHERE REQUEST_ID = @REQUEST_ID AND STATUS !=@STATUS ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", RequestID), new SqlParameter("@STATUS", Status) };
            return Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras));
        }

        public int GetCycleCountRequestStatus(DataBase dataBase, DbTransaction tran, string RequestID, string ReleaseID, string Status, string whLoginID)
        {
            string strSql = " SELECT COUNT(1) FROM @_@CYCLE_COUNT_TAG WHERE REQUEST_ID = @REQUEST_ID AND RELEASE_ID=@RELEASE_ID AND STATUS !=@STATUS ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", RequestID), new SqlParameter("@STATUS", Status), new SqlParameter("@RELEASE_ID", ReleaseID) };
            return Convert.ToInt32(dataBase.ExecuteScalar(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras));
        }

        public CycleCountTagInfo GetCycleCountTagByID(string ReleaseID, string TagNo, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID) + " WHERE RELEASE_ID = @RELEASE_ID AND TAG_NO = @TAG_NO  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", ReleaseID), new SqlParameter("@TAG_NO", TagNo), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            CycleCountTagInfo cycleCountTagInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    cycleCountTagInfo = new CycleCountTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RELEASE_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, reader["REQUEST_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["STATUS_D"].ToString());
                }
            }
            return cycleCountTagInfo;
        }

        public CycleCountTagInfo GetCycleCountTagByID(DataBase dataBase, DbTransaction tran, string RequestID, string TagNo, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID) + " WHERE REQUEST_ID = @REQUEST_ID AND TAG_NO = @TAG_NO  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REQUEST_ID", RequestID), new SqlParameter("@TAG_NO", TagNo), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            CycleCountTagInfo cycleCountTagInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                cycleCountTagInfo = new CycleCountTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RELEASE_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, reader["REQUEST_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return cycleCountTagInfo;
        }

        public DataSet GetCycleCountTagByQueryList(List<string> sqlWhere, CycleCountTagQueryEntity cyclecounttagQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!cyclecounttagQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, cyclecounttagQuery.CurrentPage, cyclecounttagQuery.PageSize, cyclecounttagQuery.SortField, cyclecounttagQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetCycleCountTagByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, CycleCountTagQueryEntity cyclecounttagQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!cyclecounttagQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, cyclecounttagQuery.CurrentPage, cyclecounttagQuery.PageSize, cyclecounttagQuery.SortField, cyclecounttagQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<CycleCountTagInfo> GetCycleCountTagByReleaseID(string ReleaseID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CYCLE_COUNT_TAG.Replace("@_@", whLoginID) + " WHERE CCT.RELEASE_ID = @RELEASE_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", ReleaseID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            List<CycleCountTagInfo> cycleCountTagInfoList = new List<CycleCountTagInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    CycleCountTagInfo cycleCountTagInfo = new CycleCountTagInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RELEASE_ID"].ToString(), (reader["TAG_NO"].ToString() != "") ? int.Parse(reader["TAG_NO"].ToString()) : 0, reader["REQUEST_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["SYS_QTY"].ToString() != "") ? decimal.Parse(reader["SYS_QTY"].ToString()) : 0M, (reader["COUNT_QTY"].ToString() != "") ? decimal.Parse(reader["COUNT_QTY"].ToString()) : 0M, reader["COUNT_BY"].ToString(), (reader["COUNT_DATE"].ToString() != "") ? DateTime.Parse(reader["COUNT_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ADJ_QTY"].ToString() != "") ? decimal.Parse(reader["ADJ_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["STATUS_MSG"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS_D"].ToString());
                    cycleCountTagInfoList.Add(cycleCountTagInfo);
                }
            }
            return cycleCountTagInfoList;
        }

        public int InsertCycleCountTag(CycleCountTagInfo cycleCountTagInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                cycleCountTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountTag_Parameters(cycleCountTagInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertCycleCountTag(DataBase dataBase, DbTransaction tran, CycleCountTagInfo cycleCountTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                cycleCountTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountTag_Parameters(cycleCountTagInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_CycleCountTag_Parameters(CycleCountTagInfo cycleCountTagInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RELEASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TAG_NO, SqlDbType.Int, 4), new SqlParameter(this.REQUEST_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 20), new SqlParameter(this.LOC, SqlDbType.VarChar, 20), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SYS_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.COUNT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.COUNT_BY, SqlDbType.VarChar, 20), new SqlParameter(this.COUNT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ADJ_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS_MSG, SqlDbType.VarChar, 0xff), 
                new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(cycleCountTagInfo.WhID))
            {
                paramArray[0].Value = cycleCountTagInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = cycleCountTagInfo.ReleaseID;
            paramArray[2].Value = cycleCountTagInfo.TagNo;
            if (!string.IsNullOrEmpty(cycleCountTagInfo.RequestID))
            {
                paramArray[3].Value = cycleCountTagInfo.RequestID;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = cycleCountTagInfo.OwnerID;
            paramArray[5].Value = cycleCountTagInfo.SkuID;
            paramArray[6].Value = cycleCountTagInfo.Lot;
            paramArray[7].Value = cycleCountTagInfo.Loc;
            paramArray[8].Value = cycleCountTagInfo.LpnID;
            paramArray[9].Value = cycleCountTagInfo.SysQty;
            paramArray[10].Value = cycleCountTagInfo.CountQty;
            if (!string.IsNullOrEmpty(cycleCountTagInfo.CountBy))
            {
                paramArray[11].Value = cycleCountTagInfo.CountBy;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (cycleCountTagInfo.CountDate != new DateTime())
            {
                paramArray[12].Value = cycleCountTagInfo.CountDate;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            paramArray[13].Value = cycleCountTagInfo.AdjQty;
            paramArray[14].Value = cycleCountTagInfo.Status;
            paramArray[15].Value = cycleCountTagInfo.StatusMsg;
            paramArray[0x10].Value = cycleCountTagInfo.CreatedBy;
            paramArray[0x11].Value = cycleCountTagInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateCycleCountTag(CycleCountTagInfo cycleCountTagInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                cycleCountTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountTag_Parameters(cycleCountTagInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateCycleCountTag(DataBase dataBase, DbTransaction tran, CycleCountTagInfo cycleCountTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                cycleCountTagInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_CycleCountTag_Parameters(cycleCountTagInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CYCLE_COUNT_TAG.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateCycleCountTagQty(DataBase dataBase, DbTransaction tran, CycleCountTagInfo cycleCountTagInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RELEASE_ID", cycleCountTagInfo.ReleaseID), new SqlParameter("@TAG_NO", cycleCountTagInfo.TagNo), new SqlParameter("@COUNT_BY", cycleCountTagInfo.CountBy), new SqlParameter("@COUNT_DATE", cycleCountTagInfo.CountDate), new SqlParameter("@COUNT_QTY", cycleCountTagInfo.CountQty), new SqlParameter("@ADJ_QTY", cycleCountTagInfo.AdjQty), new SqlParameter("@UPDATED_BY", cycleCountTagInfo.UpdatedBy), new SqlParameter("@STATUS", cycleCountTagInfo.Status) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CYCLE_COUNT_TAG_RELEASE.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

